-- @author lixiyong01
-- @date 2023.05.11
-- 字段备注不规范告警, 严重级别 1-电话 2-短信 3-消息

use hdp_lbg_supin_zplisting;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool-1.0.18.1-shaded.jar;
create temporary function SendOndutyUDF AS 'com.bj58.adsp.dp.hive.offline_udf.SendOndutyUDF';

-- SendOndutyUDF： eventId,subject,description,responser,severity,rgId,ctiId,agentId,agentSecret,notifyRg,notifyAdmin
insert overwrite table ads_zp_rock_husky_column_check_2onduty partition (dt=${dateSuffix},task_id='0')
select
	event_id,
	responser, rg_id, cti_id, subject, detail,
	SendOndutyUDF(event_id, subject, detail, responser, severity_level, rg_id, cti_id, agent_id, '000000', 0, 1) as send_result
from (
select
    event_id,
    responser,
    'rg-20220408165440319qtek0b' as rg_id,
    '202312151026086152m0y3i' as cti_id,
    'app_zp_rock_column_20231215001' as agent_id,
    3 as severity_level,
    '字段备注不规范告警' as subject,
    detail
from (
    select
        concat(from_unixtime(unix_timestamp(),'yyyyMMddHHmmss'),cast(rand() * 100000000 as int)) as event_id,
        a.username as responser,
        team_name_cn as team,
        concat_ws('#',detail_list) as detail
    from (
        select username, table_name,
            collect_list(
                concat('表名称：',database_name,'.',table_name,', 创建时间：',create_time, ', DP表链接：', 'https://dp.58corp.com/data-map/detail-page/',table_id)
            ) as detail_list
        from (
            select table_id,
                nvl(database_name,'-') as database_name,
                nvl(table_name,'-') as table_name,
                owner_id as username,
                create_time
            from hdp_lbg_supin_zplisting.ads_zp_rock_husky_column_check
            where dt='${#date(0, 0, -1):yyyyMMdd#}'
        ) n
        where username!='null'
        group by username,table_name
    ) a
    left join (
        select
            username,
            realname as user_name_cn,
            replace(org_cn_path,'\\','|') as team_name_cn
        from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
        where dt = '${#date(0, 0, -1):yyyyMMdd#}'
    ) b on a.username = b.username
) t
where team like '%技术支撑线|平台工程技术部|平台数据部%'
limit 30
) t0;